package org.ekingstar.qd.service.impl;

import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.ekingstar.qd.domain.QdColumnInfo;
import org.ekingstar.qd.domain.QdCompareCond;
import org.ekingstar.qd.domain.QdCompareType;
import org.ekingstar.qd.domain.QdDomain;
import org.ekingstar.qd.domain.QdHisCond;
import org.ekingstar.qd.domain.QdSysParam;
import org.ekingstar.qd.domain.QdWhereClause;
import org.ekingstar.qd.service.CConditionService;

import com.ekingstar.framework.domain.User;
import com.ekingstar.framework.security.UserUtil;

public class CConditionServiceImpl implements CConditionService {
	private HibernateTemplate hibernateTemplate;
	public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
		this.hibernateTemplate = hibernateTemplate;
	}
	
	@SuppressWarnings("unused")
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	/**
	 * @descripte 根据查询类别ID号,获取查询类别信息 
	 * @param strCompareTypeId 查询类别ID号集	
	 * @return List
	 */
	public List<QdCompareType> getCompareTypeList(String[] strCompareTypeId){
		List<QdCompareType> retList = null;
		try{
			if(strCompareTypeId!=null && strCompareTypeId.length>0){
				String inCondition = "";
				for(int i=0;i<strCompareTypeId.length;i++){
					if(strCompareTypeId[i]!=null && !"".equals(strCompareTypeId[i].toString().trim())){
						if(inCondition!=null && !"".equals(inCondition.toString().trim())){
							inCondition = inCondition+",'"+strCompareTypeId[i].toString().trim()+"'";
						}else{
							inCondition = "'"+strCompareTypeId[i].toString().trim()+"'";
						}
					}
				}
				
				String hql = " from QdCompareType where isvalid=1 ";
				if(inCondition!=null && !"".equals(inCondition.toString().trim())){
					hql = hql +" and id in ("+inCondition+") order by orderid ";
				}else{
					hql += " and 1<1 "; 
				}
				
				retList = hibernateTemplate.find(hql);
			}
		}catch(Exception e){
			e.printStackTrace();
			retList = null;
		}
		return retList;
	}
	
	/**
	 * @descripte 获取所有查询类别信息 
	 * @param 无	
	 * @return List
	 */
	@SuppressWarnings("unchecked")
	public List<QdCompareType> getCompareTypeList(){
		List<QdCompareType> retList = null;
		try{
			String hql = " from QdCompareType where isvalid=1 order by orderid ";
			retList = hibernateTemplate.find(hql);
		}catch(Exception e){
			e.printStackTrace();
			retList = null;
		}
		return retList;
	}
	
	/**
	 * @descripte 根据主题id号，获取该主题的所有查询条件信息 
	 * @param strDomainId 主题ID号	
	 * @return List
	 */	
	@SuppressWarnings("unchecked")
	public List<QdCompareCond> getCompareCondList(String strDomainId){
		List<QdCompareCond> retList = null;
		if(strDomainId!=null && !"".equals(strDomainId)){
			String hql = " from QdCompareCond qdCompareCond where qdCompareCond.TQdDomain.id='"+strDomainId+"'";
			retList = hibernateTemplate.find(hql);
		}				
		return retList;
	}
	
	
	/**
	 * @descripte 根据查询条件ID号获取查询条件
	 * @param strConditionId  查询条件ID号
	 * @return QdCompareCond
	 */
	public QdCompareCond getCompareCond(String strConditionId){
		QdCompareCond qdCompareCond = null;
		if(strConditionId!=null && !"".equals(strConditionId)){
			qdCompareCond = (QdCompareCond)hibernateTemplate.get(QdCompareCond.class, strConditionId);
		}		
		return qdCompareCond;
	}
	
	
	/**
	 * @descripte 根据字段ID号获取查询SQL
	 * @param strFieldId      字段ID号
	 * @param nConditionType  条件类型 (1/2: 下拉框/多选框;4:树形复选)
	 * @return String
	 */
	public String getCompareCondSqlByFiedlId(String strFieldId,int nConditionType){
		String sql = "";
		if(strFieldId!=null && !"".equals(strFieldId)){
			QdColumnInfo qdColumnInfo = (QdColumnInfo)hibernateTemplate.get(QdColumnInfo.class,strFieldId);
			if(qdColumnInfo!=null && qdColumnInfo.getId()!=null && !"".equals(qdColumnInfo.getId().toString().trim())){
				String fTable = null;
				String field1 = null;
				String field2 = null;
				String field3 = null;
				String field4 = null;
				
				if(qdColumnInfo.getFkTable()!=null && !"".equals(qdColumnInfo.getFkTable().toString().trim())){
					 fTable = qdColumnInfo.getFkTable().toString().trim();
				}
				if(qdColumnInfo.getFkField1()!=null && !"".equals(qdColumnInfo.getFkField1().toString().trim())){
					field1 = qdColumnInfo.getFkField1().toString().trim();
				}
				if(qdColumnInfo.getFkField2()!=null && !"".equals(qdColumnInfo.getFkField2().toString().trim())){
					field2 = qdColumnInfo.getFkField2().toString().trim();
				}
				if(qdColumnInfo.getFkField3()!=null && !"".equals(qdColumnInfo.getFkField3().toString().trim())){
					field3 = qdColumnInfo.getFkField3().toString().trim();
				}
				if(qdColumnInfo.getFkField4()!=null && !"".equals(qdColumnInfo.getFkField4().toString().trim())){
					field4 = qdColumnInfo.getFkField4().toString().trim();
				}				
				
				if(nConditionType==1 || nConditionType==2){
					if(fTable!=null && !"".equals(fTable) && field1!=null && !"".equals(field1) && field2!=null && !"".equals(field2)){
						sql = " select distinct " +field1 +" as fill_value , "+field2 +" as fill_text from " +fTable;
					}
				}
				
				if(nConditionType==4){ 
					if(fTable!=null && !"".equals(fTable) && field1!=null && !"".equals(field1) && field2!=null && !"".equals(field2) && field3!=null && !"".equals(field3)  && field4!=null && !"".equals(field4)){
						sql = " select distinct " + field1 +" as node_id, "+ field2 +" as node_text, "+ field3 +" as parent_node_id, "+ field4 +" as orderno from " +fTable +" order by "+ field4;
					}
				}
			}
		}
		
		return sql;
	}
	
	
	
	/**
	 * @descripte 检查SQL语句是否符合要求 
	 * @param  sql 待检查的SQL语句
	 * @return int 正确返回1,否则为 -1
	 */
	public int checkSql(String sql){
		int retInt = -1;
		try{
			if(sql!=null && !"".equals(sql.toString().trim())){
				List l = this.jdbcTemplate.queryForList(sql);
				retInt = 1;
			}
		}catch(Exception e){
			e.printStackTrace();
			retInt = -1;
		}
		return retInt;
	}
	
	
	
	/**
	 * @descripte 新增查询字段条件，成功为1,失败为-1
	 * @param strDomainId            主题ID号
	 * @param strName                字段名称
	 * @param strDescript            关系描述
	 * @param nType                  条件类型
	 * @param strSql                 字段SQL语句
	 * @param nAutoSingleQuotation   是否自动添加引号，0为否，1为是
	 * @param strFieldId             字段ID号
	 * @param colStr                 查询条件表达式
	 * @return int
	 */
	public int addCompareCond(String strDomainId,String strName,String strDescript,int nType,String strSql,int nAutoSingleQuotation,String strFieldId,String colStr){
		int retInt = -1;
		try{
			if(strDomainId!=null && !"".equals(strDomainId) && strName!=null && !"".equals(strName)	&& strFieldId!=null && !"".equals(strFieldId) && colStr!=null && !"".equals(colStr)){
				QdCompareCond qdCompareCond = new QdCompareCond();
				
				QdColumnInfo qdColumnInfo = (QdColumnInfo)this.hibernateTemplate.get(QdColumnInfo.class, strFieldId.toString().trim());				
				qdCompareCond.setTQdColumninfo(qdColumnInfo);
				
				QdDomain qdDomain  = (QdDomain)this.hibernateTemplate.get(QdDomain.class, strDomainId.toString().trim());
				qdCompareCond.setTQdDomain(qdDomain);
				
				QdCompareType qdCompareType = (QdCompareType)this.hibernateTemplate.get(QdCompareType.class, Integer.toString(nType).trim());				
				qdCompareCond.setTQdComparetype(qdCompareType);
				
				qdCompareCond.setDescript(strDescript);
				qdCompareCond.setSql(strSql);
				qdCompareCond.setName(strName);
				qdCompareCond.setAutoSingleQuotation(nAutoSingleQuotation);
				qdCompareCond.setCol(colStr);
				hibernateTemplate.save(qdCompareCond);
				retInt = 1;				
			}
		}catch(Exception e){
			e.printStackTrace();
			retInt = -1;
		}
		
		return retInt;
	}
	
	
	/**
	 * @descripte 修改查询字段条件，成功为1,失败为-1
	 * @param strConditionId         查询条件ID号
	 * @param strName                字段名称
	 * @param strDescript            关系描述
	 * @param nType                  条件类型
	 * @param strSql                 字段SQL语句
	 * @param nAutoSingleQuotation   是否自动添加引号，0为否，1为是
	 * @param strFieldId             字段ID号
	 * @param colStr                 查询条件表达式
	 * @return int
	 */
	public int updateCompareCond(String strConditionId,String strName,String strDescript,int nType,String strSql,int nAutoSingleQuotation,String strFieldId,String colStr){
		int retInt =-1;
		try{
			if(strConditionId!=null && !"".equals(strConditionId) && strName!=null && !"".equals(strName)	&& strFieldId!=null && !"".equals(strFieldId) && colStr!=null && !"".equals(colStr)){
				QdCompareCond qdCompareCond = (QdCompareCond)hibernateTemplate.get(QdCompareCond.class, strConditionId.toString().trim());
				
				QdColumnInfo qdColumnInfo = new QdColumnInfo();
				qdColumnInfo.setId(strFieldId);
				qdCompareCond.setTQdColumninfo(qdColumnInfo);				
				
				QdCompareType qdCompareType = new QdCompareType();
				qdCompareType.setId(Integer.toString(nType));
				qdCompareCond.setTQdComparetype(qdCompareType);
								
				qdCompareCond.setDescript(strDescript);
				qdCompareCond.setSql(strSql);
				qdCompareCond.setName(strName);
				qdCompareCond.setAutoSingleQuotation(nAutoSingleQuotation);
				qdCompareCond.setCol(colStr);
				hibernateTemplate.update(qdCompareCond);
				
				retInt = 1;				
			}
		}catch(Exception e){
			e.printStackTrace();
			retInt = -1;
		}
		return retInt;
	}
	
	
	/**
	 * @descripte 删除查询条件，成功返回1，否则返回-1
	 * @param strConditionId        查询条件ID号集
	 * @return int
	 */
	public int delCompareCond(String[] strConditionId){
		int retInt =-1;
		try{
			if(strConditionId!=null && strConditionId.length>0){
				for(int i=0;i<strConditionId.length;i++){
					QdCompareCond qdCompareCond = (QdCompareCond)hibernateTemplate.get(QdCompareCond.class, strConditionId[i].toString().trim());
					if(qdCompareCond!=null){
						hibernateTemplate.delete(qdCompareCond);
					}					
				}
				retInt = 1;
			}
		}catch(Exception e){
			e.printStackTrace();
			retInt = -1;
		}
		return retInt;
	}
	
		
	/**
	 * @descripte 根据主题id号，获取该主题的所有获取过滤条件
	 * @param strDomainId 主题ID号	
	 * @return List
	 */
	@SuppressWarnings("unchecked")
	public List<QdWhereClause> getWhereCauseList(String strDomainId){
		List<QdWhereClause> retList = null;
		if(strDomainId!=null && !"".equals(strDomainId.toString().trim())){
			String hql = " from QdWhereClause qdWhereClause where qdWhereClause.TQdDomain.id='"+strDomainId.toString().trim()+"'";
			retList = hibernateTemplate.find(hql);
		}
		return retList;
	}
	
	
	/**
	 * @descripte 根据过滤条件ID号获取获取过滤条件
	 * @param strWhereCauseId  过滤条件ID号
	 * @return QdWhereClause
	 */
	public QdWhereClause getWhereCause(String strWhereCauseId){
		QdWhereClause qdWhereClause = null;
		if(strWhereCauseId!=null && !"".equals(strWhereCauseId)){
			qdWhereClause = (QdWhereClause)hibernateTemplate.get(QdWhereClause.class, strWhereCauseId);
		}
		return qdWhereClause;
	}
	
	
	/**
	 * @descripte 新增过滤条件，成功返回1，否则返回-1
	 * @param strDomainId            主题ID号
	 * @param strName                条件名称
	 * @param strCondStr             条件值
	 * @return int
	 */
	public int addWhereClause(String strDomainId,String strName,String strCondStr){
		int retInt = -1;
		try{
			if(strDomainId!=null && !"".equals(strDomainId) && strName!=null && !"".equals(strName) && strCondStr!=null && !"".equals(strCondStr)){
				QdWhereClause qdWhereClause = new QdWhereClause();
				
				QdDomain qdDomain  =(QdDomain)this.hibernateTemplate.get(QdDomain.class, strDomainId.toString().trim());			
				qdWhereClause.setTQdDomain(qdDomain);
				
				qdWhereClause.setName(strName);
				qdWhereClause.setCondstr(strCondStr);
				
				hibernateTemplate.save(qdWhereClause);
				
				retInt = 1;				
			}
		}catch(Exception e){
			e.printStackTrace();
			retInt = -1;
		}
		
		return retInt;
	}
	
	
	/**
	 * @descripte 修改过滤条件，成功返回1，否则返回-1
	 * @param strWhereCauseId        过滤条件ID号
	 * @param strName                条件名称
	 * @param strCondStr             条件值
	 * @return int
	 */
	public int updateWhereClause(String strWhereCauseId,String strName,String strCondStr){
		int retInt = -1;
		try{
			if(strWhereCauseId!=null && !"".equals(strWhereCauseId) && strName!=null && !"".equals(strName) && strCondStr!=null && !"".equals(strCondStr)){
				QdWhereClause qdWhereClause = (QdWhereClause)hibernateTemplate.get(QdWhereClause.class, strWhereCauseId);
							
				qdWhereClause.setName(strName);
				qdWhereClause.setCondstr(strCondStr);
				
				hibernateTemplate.update(qdWhereClause);
				
				retInt = 1;				
			}
		}catch(Exception e){
			e.printStackTrace();
			retInt = -1;
		}
		
		return retInt;
	}
	
	
	/**
	 * @descripte 删除过滤条件，成功返回1，否则返回-1
	 * @param strWhereCauseId        过滤条件ID号
	 * @return int
	 */
	public int delWhereClause(String[] strWhereCauseId){
		int retInt = -1;
		try{
			if(strWhereCauseId!=null && strWhereCauseId.length>0){
				for(int i=0;i<strWhereCauseId.length;i++){
					QdWhereClause qdWhereClause = (QdWhereClause)hibernateTemplate.get(QdWhereClause.class, strWhereCauseId[i].toString().trim());
					if(qdWhereClause!=null){
						hibernateTemplate.delete(qdWhereClause);
					}
				}
				retInt = 1;
			}			
		}catch(Exception e){
			e.printStackTrace();
			retInt = -1;
		}
		return retInt;
	}
		
	
	/**
	 * @descripte 根据主题id号，获取该主题的所有历史条件
	 * @param strDomainId 主题ID号	
	 * @return List
	 */
	@SuppressWarnings("unchecked")
	public List<QdHisCond> getHisConditionList(String strDomainId){
		List<QdHisCond> retList = null;
		if(strDomainId!=null && !"".equals(strDomainId)){
			String hql = " from QdHisCond qdHisCond where qdHisCond.TQdDomain.id='"+strDomainId+"'";
			retList = hibernateTemplate.find(hql);
		}
		return retList;	
	}
	

	/**
	 * @descripte 根据主题id号和用户登录号，获取历史条件信息
	 * @param strDomainId 主题ID号	
	 * @return List
	 */
	@SuppressWarnings({ "unchecked", "unchecked" })
	public List<QdHisCond> getHisConditionByUserList(String strDomainId){
		List<QdHisCond> retList = null;
		try{ 
			String userId =UserUtil.getCurrentUserId();
			if(userId!=null && !"".equals(userId.toString().trim()) && strDomainId!=null && !"".equals(strDomainId)){
				String hql = " from QdHisCond qdHisCond where qdHisCond.TQdDomain.id='"+strDomainId+"' and qdHisCond.userId='"+userId.toString().trim()+"' order by qdHisCond.createtime desc";
				retList = hibernateTemplate.find(hql);
			}
		}catch(Exception e){
			e.printStackTrace();
			retList = null;
		}
		
		return retList;
	}
	
	
	/**
	 * @descripte 根据历史条件ID号获取获取历史条件
	 * @param strHisConditionId  历史条件ID号
	 * @return QdHisCond
	 */
	public QdHisCond getHisCondition(String strHisConditionId){
		QdHisCond qdHisCond = null;
		if(strHisConditionId!=null && !"".equals(strHisConditionId)){
			qdHisCond = (QdHisCond)hibernateTemplate.get(QdHisCond.class, strHisConditionId);
		}
		return qdHisCond;
	}
	
	
	/**
	 * @descripte 新增历史查询条件，成功返回1，否则返回-1
	 * @param strDomainId            主题ID号
	 * @param strName                条件名称
	 * @param strDescript            条件描述
	 * @param strCondStr             条件值
	 * @param strCondStrCn           条件中文描述
	 * @param fieldstr               查询列ID
	 * @param orderstr               排序列ID
	 * @param orderstrtype           排序方式
	 * @param contype                条件类型
	 * @return int
	 */
	public int addHisCondition(String strDomainId,String strName,String strDescript,String strCondStr,String strCondStrCn,String fieldstr,String orderstr,String orderstrtype,String contype){
		int retInt = -1;
		try{
			String userId = UserUtil.getCurrentUserId();		
			if(userId!=null  && !"".equals(userId.toString().trim()) && strDomainId!=null  && !"".equals(strDomainId) && 	strName!=null && !"".equals(strName)){
				QdHisCond qdHisCond = new QdHisCond();
				
				QdDomain qdDomain  = (QdDomain)this.hibernateTemplate.get(QdDomain.class, strDomainId.toString().trim());
				qdHisCond.setTQdDomain(qdDomain);
				
				qdHisCond.setDescript(strDescript);
				qdHisCond.setCondstr(strCondStr);
				qdHisCond.setCondname(strName);
				qdHisCond.setCondstrcn(strCondStrCn);
				qdHisCond.setUserId(userId);
				qdHisCond.setCreatetime(new Date());
				
				qdHisCond.setFieldstr(fieldstr);
				qdHisCond.setOrderstr(orderstr);
				qdHisCond.setOrdertypestr(orderstrtype);
				qdHisCond.setContype(contype);
				
				hibernateTemplate.save(qdHisCond);
				
				retInt = 1;				
			}else{
				userId = null;
			}
		}catch(Exception e){
			e.printStackTrace();
			retInt = -1;
		}		
		return retInt;
	}
	
	
	/**
	 * @descripte 修改历史查询条件，成功返回1，否则返回-1
	 * @param strHisConditionId      历史条件ID号
	 * @param strName                条件名称
	 * @param strDescript            条件描述
	 * @param strCondStr             条件值
	 * @param strCondStrCn           条件中文描述
	 * @return int
	 */
	public int updateHisCondition(String strHisConditionId,String strName,String strDescript,String strCondStr,String strCondStrCn){
		int retInt = -1;
		try{				
			if(strHisConditionId!=null &&  !"".equals(strHisConditionId) && strName!=null && !"".equals(strName) && strCondStr!=null && !"".equals(strCondStr)){
				QdHisCond qdHisCond = (QdHisCond)hibernateTemplate.get(QdHisCond.class, strHisConditionId);
				if(qdHisCond!=null && qdHisCond.getId()!=null && !"".equals(qdHisCond.getId().toString().trim())){
					qdHisCond.setDescript(strDescript);
					qdHisCond.setCondstr(strCondStr);
					qdHisCond.setCondname(strName);
					qdHisCond.setCondstrcn(strCondStrCn);	
					
					hibernateTemplate.update(qdHisCond);
					
					retInt = 1;	
				}		
			}
		}catch(Exception e){
			e.printStackTrace();
			retInt = -1;
		}		
		return retInt;
	}
	
	
	/**
	 * @descripte 删除历史查询条件，成功返回1，否则返回-1
	 * @param strHisConditionId        历史条件ID号集
	 * @return int
	 */
	public int delHisCondition(String[] strHisConditionId){
		int retInt = -1;
		try{
			if(strHisConditionId!=null && strHisConditionId.length>0){
				for(int i=0;i<strHisConditionId.length;i++){
					QdHisCond qdHisCond = (QdHisCond)hibernateTemplate.get(QdHisCond.class, strHisConditionId[i].toString().trim());
					if(qdHisCond!=null){
						hibernateTemplate.delete(qdHisCond);
					}
				}
				retInt = 1;
			}			
		}catch(Exception e){
			e.printStackTrace();
			retInt = -1;
		}
		return retInt;
	}
	
	
	/**
	 * @descripte 获取系统变量	
	 * @return List
	 */
	public List<QdSysParam> getQdSysParamList(){
		List<QdSysParam> qdSysParamList = null;
		try{
			String hql = "from  QdSysParam where isvalid='1' order by orderId " ;
			qdSysParamList = hibernateTemplate.find(hql);			
		}catch(Exception e){
			e.printStackTrace();
			qdSysParamList = null;
		}
		return qdSysParamList;
	}
	
	/**
	 * @descripte 根据系统变量ID获取系统变量	
	 * @param  id 系统变量ID
	 * @return QdSysParam
	 */
	public QdSysParam getQdSysParam(String id){
		QdSysParam qdSysParam = null;
		if(id!=null && !"".equals(id)){
			qdSysParam = (QdSysParam)hibernateTemplate.get(QdSysParam.class, id);
		}
		return qdSysParam;
	}

}
